現在我們的資料庫已經就緒了，我們趕快來透過 .NET Web API 操作資料庫吧！

現在寫 .NET 操作資料庫通常都會用 ORM（Object Relation Map）套件幫我們對應我們的 C# 物件 與資料庫之間的關係。目前 .NET 生態中最流行大概是以下兩個：

• Entity Framework
  微軟力推的強大 ORM，非常的完整功能強大，搭配 LINQ 語法操作資料庫，只要安裝不同的提供器（provider），就能用同一份 C# 程式操作不同的資料庫。
• Dapper
  輕量級的 ORM，必須要自己定義 C# class、管理連線、自己寫 SQL，但是套件非常迷你而且效能非常好，很適合已經學會目標資料庫語法的開發者。

ORM 的選擇其實與團隊還有「信仰」比較有關，筆者自己使用的感覺是：EF 真的很方便，Dapper 真的很快。本系列因為介紹過 MySQL 的語法，所以會使用 Dapper 來示範，偷渡一下推廣這個輕巧、高效能的 ORM。

參數化查詢

在實際使用 Dapper 之前，我們先來聊聊參數化查詢。參數化查詢簡單來說就是不要直接用字串寫上 SQL 指令所需要的值，而是在指令中給一個代號，接著再讓我們程式中的物件，根據代號把查詢所需要的值帶入指令的代號中。例如

var queryParams = new { UserId = 1 };
var targetUser = dbConnection.Query(
    "SELECT * FROM `user` WHERE user_id = @UserId",
    queryParams
);

上面的程式碼中，第一行宣告了一個物件，裡面只有一個值為 1 的 UserId。第二行執行 SQL 指令，嘗試從 user 資料表取出 user_id = 1 的資料，這個查詢的字串中我們並沒有直接給值，而是給了一個代號 @UserId，待會真正要查詢的時候，我們才會從 Query() 方法的第二個參數 queryParams 裡面，找出名字為 UserId 的屬性，並把值取出來，最後送到資料庫，由資料庫幫我們把值帶入 SQL 指令並執行。

參數化查詢最主要的好處是可以有效杜絕 SQL 注入（injection）攻擊，SQL 注入簡單來說就是在給值的時候，偷偷藏了可以執行的 SQL 指令在裡面，這樣資料庫在值型我們的查詢的時候，就有可能執行被偷藏在裡面的有害指令，造成我們的資料外洩或損毀。

安裝 Dapper

拖了這麼久，終於要開始使用 Dapper 的章節了！首先，我們需要安裝相關的套件

1. Dapper
2. MySQL Connector 或者 MySQL Data

MySQL Connect 與 MySQL Data 用法基本上都一樣，只是 MySQL Connector 是社群在維護，授權是 MIT；MySQL Data 是 Oracle 官方在維護，授權是 GPLv2

首先對我們的專案點右鍵 -> 管理 NuGet 套件，然後在瀏覽那邊搜尋這兩個套件並安裝。基本上直接裝最新版就好不必刻意選版本，有很舊的專案再注意一下相依版本就好。

Dapper 基礎用法

裝完之後就可以開始來用 Dapper 了，Dapper 的基本用法包含四個步驟

1. 定義一個用來接資料的 Model 類別
   Dapper 支援泛型，只要把指定的型別告訴 Dapper，而且類別的屬性與 MySQL 的欄位有對上，Dapper 就會自動幫我們把取回來的資料放進 Model 類別。開發時期資料還不固定可以暫時用 dynamic，但是固定下來之後建議還是宣告一個 Model Class 來儲存，因為 dynamic 型別比較容易出錯、不容易維護。

MySQL 與 .NET 的命名慣例不同，MySQL 大多使用小寫英文 + 下底線，.NET 大多使用駝峰式命名，大多數的狀況無法直接對應上，這時候只要使用 AS 語法改變選取結果的名稱，就能輕鬆地對上。例如
SELECT user_naem AS UserName FROM `user` WHERE user_id = 1;

2. 開啟一個 MySQL 連線
   用 var _db = new MySqlConnection("連線資訊"); 就能開啟連線。比較需要注意的是，最好用注入組態設定的方法取得連線資訊，以及用 using 語法自動幫我們關閉連線，以免忘記關閉造成連線被占用。

3. 執行 SQL 指令
   Dapper 有多種執行 SQL 指令的語法，以下對常用的幾個語法做說明

   • 查詢多筆資料
     用 Query<> 泛型方法，執行完 SQL 查詢完成後，Dapper 會自己幫我們把資料對應到 Model 類別

   var results = _db.Query<Model類別>(“查詢多筆資料的 SQL 指令”).ToList();
   

   • 查詢一個純量
     使用 ExecuteScalar<> 泛型方法，查詢完成後 Dapper 會自動把純量轉型。

   var result = _db. ExecuteScalar<資料型態>(“查詢純量的 SQL 指令”);
   

   • 執行 SP 並取得回傳值（SP 最後 SELECT 的資料）
     同樣使用 Query<> 泛型方法，但需要指定 commandType 為 StoredProcedure

   var spParams = new { 參數名稱1: 參數值1, 參數名稱2: 參數值2, …};
   var results = _db.Query<Model類別>("SP名稱", spParams, commandType: CommandType.StoredProcedure);
   

   • 執行 SP 但不接資料
     使用 Execute() 方法，一樣要指定 commandType 為 StoredProcedure

   _db. Execute("SP名稱", spParams, commandType: CommandType.StoredProcedure);
   

   • 執行 SP 並取得 OUT 參數的值
     使用 Execute() 方法搭配 DynamicParameters 類別儲存參數，OUT 參數需要額外再指定 direction 為 Output，一樣要指定 commandType 為 SP。執行完畢後，可以從 DynamicParameters 透過 Get 泛型方法，把結果的值取出。

   var spParams = new DynamicParameters();
   spParams.Add("IN參數名稱1", IN參數值1);
   spParams.Add("IN參數名稱2", IN參數值2);
   ...
   spParams.Add("OUT參數名稱1", OUT參數值2, direction: ParameterDirection.Output);
   spParams.Add("OUT參數名稱2", OUT參數值2, direction: ParameterDirection.Output);
   ...
   
   _db.Execute("SP名稱", spPrams, commandType: CommandType.StoredProcedure);
   
   var outParam1 = spParams.Get<資料型態1>("OUT參數名稱1");
   var outParam2 = spParams.Get<資料型態2>("OUT參數名稱2");
   ...
   

   4 關閉連線
   如果前面開啟連線沒有使用 using 關鍵字，務必記得手動關閉連線
   db?.Close()

用 Dapper 做一個 User Service

請各位回味一下本系列關於依賴注入的文章，我們現在要來建立另一個實作 IUserCRUD 介面的 Service，用 MySQL 管理使用者資料。

首先，因應資料庫的變更修改我們的 User 類別

public class User
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public bool Verified { get; set; } // 加入這個屬性
}

再來，新增 MySQL 需要的連線資訊到 appsettings.json 與 appsettings.Development.json

// appsettings.json 
// 其他不變
"MySqlOptions": {
    "ConnectionString": "Server=xxx;Database=xxx;Uid=xxx;Pwd=xxx;" // 要上版控前記得挖掉密碼
}

// appsettings.Development.json 
// 其他不變
"MySqlOptions": {
    "ConnectionString": "Server=localhost;Database=world;Uid=root;Pwd=root;" // 本機用的不會上版控，可以放心打密碼
}

接著，宣告一個 MySqlOption 類別，用來儲存 appsettings 裡的設定值，並在 Startup.cs 註冊 MySQL 需要用到的 Option

public class MySqlOptions
{
    public string ConnectionString { get; set; }
}

services.Configure<MySqlOptions>(
    Configuration.GetSection("MySqlOptions"));

主角終於登場了！宣告一個 UserServiceWithMySQL 並實作 IUserCRUD 介面，透過 Dapper 操作 MySQL

public class UserServiceWithMySQL : IUserCRUD
{
    private readonly IOptions<MySqlOptions> _options;

    public UserServiceWithMySQL(IOptions<MySqlOptions> options)
    {
        _options = options;
    }

    public void CreateUser(User model)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            bool emailUsed = db.ExecuteScalar<bool>(
                @"SELECT EXISTS 
                    (SELECT user_id FROM `user` WHERE email = @Email)",
                model);

            if (!emailUsed)
            {
                db.Execute(
                    @"INSERT INTO `user` (user_name, email, verified)
                      VALUES (@UserName, @Email, 0)",
                    model);
            }
        }
    }

    public void DeleteUser(int id)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            db.Execute(
                @"DELETE FROM `user`
                  WHERE user_id = @UserId",
                new { UserId = id});
        }
    }

    public List<User> GetAllUsers()
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            var result = db.Query<User>(
                @"SELECT
                    user_id AS UserID,
                    user_name AS UserName,
                    email AS Email,
                    verified AS Verified
                  FROM `user`",
                new {});
            return result.ToList();
        }
    }

    public User GetUserById(int id)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            var result = db.QuerySingleOrDefault<User>(
                @"SELECT
                    user_id AS UserID,
                    user_name AS UserName,
                    email AS Email,
                    verified AS Verified
                  FROM `user`
                  WHERE user_id = @UserId",
                new { UserId = id});
            return result;
        }
    }

    public void UpdateUser(int id, User model)
    {
        using (var db = new MySqlConnection(_options.Value.ConnectionString))
        {
            bool emailUsed = db.ExecuteScalar<bool>(
                @"SELECT EXISTS 
                    (SELECT user_id FROM `user` WHERE email = @UserId)",
                model);

            if (!emailUsed)
            {
                db.Execute(
                    @"UPDATE `user` 
                      SET
                        user_name = @UserName,
                        email = @Email,
                        verified = @Verified
                      WHERE
                        user_id = @UserId",
                    model);
            }
        }
    }
}

寫完 Service 之後，到 Startup.cs 換掉註冊的 UserService

// 把原本的 UserService 替換成 UserServiceWithMySQL
services.AddScoped<IUserCRUD, UserServiceWithMySQL>();

因為我們的 Controller 依賴 IUserCRUD，而我們新的 service 有實作 IUserCRUD，所以 Controller 完全不用改，F5 執行就直接把使用者的管理換到 MySQL，舒服！

明天，我們將短暫的再次回到雲端，在我們的 VM 上面裝 MySQL，然後調整環境，讓佈署在上面的 API 程式存取同一個 VM 上的 MySQL。